package com.salesmonitoring.accounts.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;
import java.util.concurrent.ArrayBlockingQueue;

import com.salesmonitoring.accounts.model.Customer;
import com.salesmonitoring.accounts.model.User;
import com.salesmonitoring.connector.Connector;
import com.salesmonitoring.model.RepairShop;

public class AdminDaoImpl {

	private static AdminDaoImpl adminDaoImpl;
	private Connector connector;
	private Connection connection;
	private Statement statement;
	private ResultSet rs;
	private String query;
	
	public static synchronized AdminDaoImpl getInstance() {
		
		if(adminDaoImpl == null) {
			adminDaoImpl = new AdminDaoImpl();
		}
		
		return adminDaoImpl;
		
	}
	
	public int addUser(ArrayList<Object> details) {
		
		int result = 0;
		
		try {
			
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "INSERT INTO user(user_id, user_password, user_name, user_role, user_warehouse, " +
					"user_email, user_contact) VALUES('"+ details.get(0)+"', '"+ details.get(1)+"', " +
					"'"+ details.get(2)+"', '"+ details.get(3)+"', '"+ details.get(4)+"', " +
					"'"+ details.get(5)+"', "+ details.get(6)+")";
			
			result = statement.executeUpdate(query);
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
		}
		
		return result;
		
	}

	public int updateUser(User user) {
		
		int result = 0;
		
		try {
			
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "UPDATE user SET user_name='"+ user.getName() +"', user_role='"+ user.getRole() +"', user_warehouse='"+ user.getWarehouse() +"', user_email='"+ user.getEmail() +"', user_contact='"+ user.getContact() +"' " +
					"WHERE user_id='"+ user.getId() +"' ";
			
			result = statement.executeUpdate(query);
			
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
		}
		
		return result;
	}
	
	public int deleteUser(String username) {
		
		int result = 0;
		
		try {
			
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "DELETE FROM user WHERE user_id='"+username+"'";
			
			result = statement.executeUpdate(query);
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
		}
	
		
		return result;
	}
	
	public String[] getAllRoles() {
	
		ArrayList<String> rolesList = new ArrayList<String>();
		String[] roles = null;
		
		try {
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "SELECT distinct user_role FROM user";
			rs = statement.executeQuery(query);
			
			rolesList.add("");
			while(rs.next()) {
				rolesList.add(rs.getString("user_role"));
			}
			
			roles = rolesList.toArray(new String[rolesList.size()]);
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			
			if(rs != null) {
				try { rs.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
		}
		
		
		return roles;
		
	}

	public String[] getAllWarehouses() {
		
		ArrayList<String> warehouseList = new ArrayList<String>();
		String[] warehouses = null;
		
		try {
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "SELECT warehouse_name FROM warehouse";
			rs = statement.executeQuery(query);
			
			warehouseList.add("");
			while(rs.next()) {
				warehouseList.add(rs.getString("warehouse_name"));
			}
			
			warehouses = warehouseList.toArray(new String[warehouseList.size()]);
			
		} catch(Exception e) {
			e.printStackTrace();
		
		} finally { 
			
			if(rs != null) {
				try { rs.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
		}
		
		
		return warehouses;
		
	}
	
	public ArrayList<User> getAllUsers() {
		
		ArrayList<User> users = new ArrayList<User>();
		
		try {
			
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "SELECT * FROM user";
			rs = statement.executeQuery(query);
			
			while(rs.next()) {
				
				User user = new User();

				user.setId(rs.getString("user_id"));
				user.setName(rs.getString("user_name"));
				user.setRole(rs.getString("user_role"));
				user.setWarehouse((rs.getString("user_warehouse") == null) ? "none" : rs.getString("user_warehouse"));
				
				users.add(user);
				
			}
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try { rs.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
		}
		
		return users;
		
	}
	
	public String[] getAllRepairShops() {
		
		ArrayList<String> rsList = new ArrayList<String>();
		String[] shops = null;
		
		rsList.add("");
		
		try {
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "SELECT rs_name FROM repair_shop";
			rs = statement.executeQuery(query);
			
			while(rs.next()) {
				rsList.add(rs.getString("rs_name"));
			}
			
			shops = rsList.toArray(new String[rsList.size()]);
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			
			if(rs != null) {
				try { rs.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
		}
		
		
		return shops;
		
		
	}
	
	public String[] getAllAccountNumbers() {
		
		ArrayList<String> accountNumbersList = new ArrayList<String>();
		
		accountNumbersList.add("");
		
		try {
			
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "select cust_account_number from customer";
			rs = statement.executeQuery(query);
			
			while(rs.next()) {
				accountNumbersList.add(rs.getString(1));
			}
			
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try { rs.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
		}
		
		String[] accountNumbers = accountNumbersList.toArray(new String[accountNumbersList.size()]);
		return accountNumbers;
		
	}
	
	public RepairShop getShopByCode(String code) {
		
		RepairShop shop = new RepairShop();
		
		try {
			
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "SELECT * FROM repair_shop WHERE rs_name='"+code+"'";  
			rs = statement.executeQuery(query);
			
			while(rs.next()) {
			
				shop.setCode(code);
				shop.setName(rs.getString("rs_name"));
				shop.setAddress(rs.getString("rs_address"));
				shop.setOwner(rs.getString("rs_owner"));
				shop.setContact(rs.getInt("rs_contact"));
				shop.setWarehouse(rs.getString("rs_warehouse"));
				
			}
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		
			if(rs != null) {
				try { rs.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(statement != null) {
				try { statement.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
			if(connection != null) {
				try { connection.close(); } catch(Exception e) { e.printStackTrace(); }
			}
			
		}
		
		return shop;
		
	}
	
	public String getCutomerbyAccountNumber(String number) {
		
		String name = "";
		
		try {
			connector = Connector.getInstance();
			connection = connector.getConnection();
			statement = connection.createStatement();
			
			query = "select cust_name from customer where cust_account_number=" + number;
			
			rs = statement.executeQuery(query);
			
			while(rs.next()) {
				name = rs.getString("cust_name");
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return name;
		
	}
	
}
